# Computations
import numpy as np
import pandas as pd

# Tools
import os
import datetime
import calendar
import itertools

# Visualisation libraries

## progressbar
import progressbar

## Text
from colorama import Fore, Back, Style
from IPython.display import Image, display, Markdown, Latex, clear_output

## plotly
from plotly.offline import init_notebook_mode, iplot 
import plotly.graph_objs as go
import plotly.offline as py
from plotly.subplots import make_subplots
import plotly.express as px

## seaborn
import seaborn as sns
sns.set_style("whitegrid")
sns.set_context("paper", rc={"font.size":12,"axes.titlesize":14,"axes.labelsize":12})

## matplotlib
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
import matplotlib.colors as mcolors
from matplotlib.patches import Ellipse, Polygon
import matplotlib.gridspec as gridspec
from pylab import rcParams
plt.style.use('seaborn-whitegrid')
import matplotlib as mpl
mpl.rcParams['figure.figsize'] = (17, 6)
mpl.rcParams['axes.labelsize'] = 14
mpl.rcParams['xtick.labelsize'] = 12
mpl.rcParams['ytick.labelsize'] = 12
mpl.rcParams['text.color'] = 'k'
%matplotlib inline

import warnings
warnings.filterwarnings("ignore")

Home / Financial Analysis and Modeling

Predict Future Sales

• Preprocessing
• Exploratory Data Analysis
• Modeling: CatBoost Regressor

Predict Future Sales

In this article, we work with the Predict Future Sales provided by one of the largest Russian software firms - 1C Company.

Data Description

You are provided with daily historical sales data. The task is to forecast the total amount of products sold in every shop for the test set. Note that the list of shops and products slightly changes every month. Creating a robust model that can handle such situations is part of the challenge.

Data_dict = {'ID': 'ID', 'City': 'The City that a shop is located',
             'City ID': 'Assigned numeric ID for a city',
             'Date':'Date in format dd/mm/yyyy', 
             'Date Block Number':'A consecutive month number, used for convenience. January 2013 is 0, february 2013 is 1,..., October 2015 is 33',
             'Day': 'Day of month', 'Day of Week': 'Day of Week', 'Item Category': 'Item Category',
             'Item Category Full':'Item Category and Subcategory Name',
             'Item Category Full ID': 'Assigned numeric ID for a Item Category',
             'Item Category ID': 'Assigned numeric ID for a Item Category',
             'Item Count Day':'The number of products sold. You are predicting a monthly amount of this measure',
             'Item ID':'Unique identifier of a product', 'Item Name':'The name of item',
             'Item Price':'Current price of an item',
             'Item Subcategory': 'Item Subcategory',
             'Item Subcategory ID': 'Assigned numeric ID for a Item Subcategory', 'Month': 'Month',
             'Month Name': 'Month Name', 'Month Triannually': 'Month Triannually', 'Revenue': 'Revenue',
             'Season': 'Season', 'Shop ID':'Unique identifier of a shop',
             'Item Category ID':'Unique identifier of item category',
             'Shop Name':'The name of shop', 'Year':'Year'}
    
def Data_fields(Data, Data_dict = Data_dict):
    Data_dict_df = pd.DataFrame({'Feature':Data.columns})
    Data_dict_df['Description'] = Data_dict_df['Feature'].map(Data_dict)
    display(Data_dict_df.style.hide_index())

def Header(Text, L = 100, C = 'Blue', T = 'White'):
    BACK = {'Black': Back.BLACK, 'Red':Back.RED, 'Green':Back.GREEN, 'Yellow': Back.YELLOW, 'Blue': Back.BLUE,
         'Magenta':Back.MAGENTA, 'Cyan': Back.CYAN}
    FORE = {'Black': Fore.BLACK, 'Red':Fore.RED, 'Green':Fore.GREEN, 'Yellow':Fore.YELLOW, 'Blue':Fore.BLUE,
         'Magenta':Fore.MAGENTA, 'Cyan':Fore.CYAN, 'White': Fore.WHITE}
    print(BACK[C] + FORE[T] + Style.NORMAL + Text + Style.RESET_ALL + ' ' + FORE[C] +
          Style.NORMAL +  (L- len(Text) - 1)*'=' + Style.RESET_ALL)
    
def Line(L=100, C = 'Blue'):
    FORE = {'Black': Fore.BLACK, 'Red':Fore.RED, 'Green':Fore.GREEN, 'Yellow':Fore.YELLOW, 'Blue':Fore.BLUE,
         'Magenta':Fore.MAGENTA, 'Cyan':Fore.CYAN, 'White': Fore.WHITE}
    print(FORE[C] + Style.NORMAL + L*'=' + Style.RESET_ALL)

def Search_List(Key, List): return [s for s in List if Key in s]

PATH = '1c_software_dataset'
Files = os.listdir(PATH)
Temp = np.sort(Files)
Files = set()
keywords = ['mod']
for key in keywords:
    Files = Files.union(set(Search_List(key, Temp)))
del Temp, key, keywords
Files = list(Files)

#
Files_Info = pd.DataFrame()
for i in range(len(Files)):
    # Read files
    Header(Files[i])
    filename = Files[i].split('.')[0]
    globals() [filename] = pd.read_csv(os.path.join(PATH, Files[i]))
    Temp = globals() [filename].shape
    Files_Info = Files_Info.append(pd.DataFrame({'File':[Files[i]], 'Number of Instances': [Temp[0]],
                                                 'Number of Attributes': [Temp[1]]}), ignore_index = True)
    # Datetime
    Cols = globals() [filename].columns
    DateTime = Search_List('date', Cols)
    if len(DateTime)>0:
        try: DateTime.remove('date_block_num')
        except: pass
        for c in DateTime:
            globals() [filename][c] = pd.to_datetime(globals() [filename][c]) 
            del c
    # Display
    display(globals() [filename].head(5))
    Data_fields(globals() [filename])
Line()
display(Files_Info.style.hide_index())
Line()
del Files, i, Temp, filename, Cols, DateTime 

shops_mod.csv ======================================================================================

	Shop Name	Shop ID	City	City ID
0	Yakutsk Ordzhonikidze, 56 fran	0	Yakutsk	0
1	Yakutsk shopping center "Central" fran	1	Yakutsk	0
2	Adygea TC "Mega"	2	Adygea	1
3	Balashikha shopping and entertainment complex ...	3	Balashikha	2
4	Volga shopping center "Volga Mall"	4	Volga	3

Feature	Description
Shop Name	The name of shop
Shop ID	Unique identifier of a shop
City	The City that a shop is located
City ID	Assigned numeric ID for a city

sales_train_mod.csv ================================================================================

	Date	Date Block Number	Shop ID	Item ID	Item Price	Item Count Day	Revenue
0	2013-02-01	0	59	22154	999.00	1.0	999.00
1	2013-03-01	0	25	2552	899.00	1.0	899.00
2	2013-05-01	0	25	2552	899.00	-1.0	-899.00
3	2013-06-01	0	25	2554	1709.05	1.0	1709.05
4	2013-01-15	0	25	2555	1099.00	1.0	1099.00

Feature	Description
Date	Date in format dd/mm/yyyy
Date Block Number	A consecutive month number, used for convenience. January 2013 is 0, february 2013 is 1,..., October 2015 is 33
Shop ID	Unique identifier of a shop
Item ID	Unique identifier of a product
Item Price	Current price of an item
Item Count Day	The number of products sold. You are predicting a monthly amount of this measure
Revenue	Revenue

items_mod.csv ======================================================================================

	Item Name	Item ID	Item Category Full ID
0	ВО ВЛАСТИ НАВАЖДЕНИЯ (ПЛАСТ.)	0	40
1	ABBYY FineReader 12 Professional Edition Full ...	1	76
2	В ЛУЧАХ СЛАВЫ (UNV)	2	40
3	ГОЛУБАЯ ВОЛНА (Univ)	3	40
4	КОРОБКА (СТЕКЛО)	4	40

Feature	Description
Item Name	The name of item
Item ID	Unique identifier of a product
Item Category Full ID	Assigned numeric ID for a Item Category

test_mod.csv =======================================================================================

	ID	Shop ID	Item ID
0	0	5	5037
1	1	5	5320
2	2	5	5233
3	3	5	5232
4	4	5	5268

Feature	Description
ID	ID
Shop ID	Unique identifier of a shop
Item ID	Unique identifier of a product

item_categories_mod.csv ============================================================================

	Item Category Full	Item Category Full ID	Item Category	Item Category ID	Item Subcategory	Item Subcategory ID
0	PC - Headsets / Headphones	0	PC	0	Headsets / Headphones	0
1	Accessories - PS2	1	Accessories	1	PS2	1
2	Accessories - PS3	2	Accessories	1	PS3	2
3	Accessories - PS4	3	Accessories	1	PS4	3
4	Accessories - PSP	4	Accessories	1	PSP	4

Feature	Description
Item Category Full	Item Category and Subcategory Name
Item Category Full ID	Assigned numeric ID for a Item Category
Item Category	Item Category
Item Category ID	Unique identifier of item category
Item Subcategory	Item Subcategory
Item Subcategory ID	Assigned numeric ID for a Item Subcategory

====================================================================================================

File	Number of Instances	Number of Attributes
shops_mod.csv	57	4
sales_train_mod.csv	2935849	7
items_mod.csv	22170	3
test_mod.csv	214200	3
item_categories_mod.csv	84	6

====================================================================================================

Exploratory Data Analysis

Merging the Datasets

def dtype_sep(Inp):
    Temp = Inp.dtypes.reset_index(drop = False)
    Temp.columns = ['Features', 'Data Type']
    Temp['Data Type'] = Temp['Data Type'].astype(str)
    # Numeric_Columns
    Numeric_Columns = Temp.loc[Temp['Data Type'].isin(['int64', 'int32', 'float64', 'float32']),'Features'].tolist()
    # Categorical_Columns
    Categorical_Columns = Temp.loc[Temp['Data Type'] == 'object','Features'].tolist()
    return Numeric_Columns, Categorical_Columns

def ToInt(df):
    Num, _ = dtype_sep(df)
    df[Num] = df[Num].astype(int)
    return df

Data = items_mod.merge(item_categories_mod, on = 'Item Category Full ID')
Data = sales_train_mod.merge(Data, on = 'Item ID')
Data = Data.merge(shops_mod, on = 'Shop ID')
# sorting
Data = Data.reindex(sorted(Data.columns), axis=1)
Data = ToInt(Data).sort_values(by = 'Date')
# display the dataset
display(Data.head(5).style.hide_index())

City	City ID	Date	Date Block Number	Item Category	Item Category Full	Item Category Full ID	Item Category ID	Item Count Day	Item ID	Item Name	Item Price	Item Subcategory	Item Subcategory ID	Revenue	Shop ID	Shop Name
Kursk	12	2013-01-01	0	PC Games	PC Games - Additional Editions	28	9	1	2257	Call Ofuty: Modern Warfare 3.LC "Коллекция 3" (Code in Box) [PC]	299	Additional Editions	14	299	19	Kursk Shopping Center "Pushkinsky"
Sergiev	20	2013-01-01	0	Gifts	Gifts - Gadgets, Robots, Sports	62	15	1	15094	Микроробот Муравей	458	Gadgets, Robots, Sports	46	458	46	Sergiev Posad Shopping Center "7Ya"
Voronezh	5	2013-01-01	0	Music	Music - Music Video	59	14	1	18525	СБОРНИК Эх Разгуляй Всенародная танцплощадка 2010VD	169	Music Video	43	169	8	Voronezh City-Park Shopping Center "Grad"
Kazan	8	2013-01-01	0	PC Games	PC Games - Additional Editions	28	9	1	2321	Call ofuty: Black Ops II. Расширенное издание [PС, русская версия]	999	Additional Editions	14	999	14	Kazan Shopping Center ParkHouse II
Moscow	13	2013-01-01	0	Gifts	Gifts - Development	67	15	1	1025	3D Crystal Puzzle Свинья XL	499	Development	51	499	27	Moscow TC "MEGA Belaya Dacha II"

Feature Engineering

Data['Date'] = pd.to_datetime(Data['Date'])
Data['Day'] = Data['Date'].dt.strftime('%d')
Data['Day of Week'] = Data['Date'].dt.strftime('%A')
Data['Month'] = Data['Date'].dt.strftime('%m')
Data['Month Triannually'] = pd.qcut(Data['Day'].astype(int), 3, labels=["First Tertile", "Second Tertile", "Third Tertile"])
Data['Season'] = pd.qcut(Data['Date'].dt.strftime('%m').astype(int), 4, labels=['Winter', 'Spring', 'Summer', 'Autumn'])
Data['Month Name'] = Data['Date'].dt.strftime('%B')
Data['Year'] = Data['Date'].dt.strftime('%Y')
# sorting
Data = Data.reindex(sorted(Data.columns), axis=1)

display(Data.head(5).style.hide_index())  
Data_dict_df = pd.DataFrame({'Feature':Data.columns})
Data_dict_df['Description'] = Data_dict_df['Feature'].map(Data_dict)
display(Data_dict_df.style.hide_index())

City	City ID	Date	Date Block Number	Day	Day of Week	Item Category	Item Category Full	Item Category Full ID	Item Category ID	Item Count Day	Item ID	Item Name	Item Price	Item Subcategory	Item Subcategory ID	Month	Month Name	Month Triannually	Revenue	Season	Shop ID	Shop Name	Year
Kursk	12	2013-01-01 00:00:00	0	01	Tuesday	PC Games	PC Games - Additional Editions	28	9	1	2257	Call Ofuty: Modern Warfare 3.LC "Коллекция 3" (Code in Box) [PC]	299	Additional Editions	14	01	January	First Tertile	299	Winter	19	Kursk Shopping Center "Pushkinsky"	2013
Sergiev	20	2013-01-01 00:00:00	0	01	Tuesday	Gifts	Gifts - Gadgets, Robots, Sports	62	15	1	15094	Микроробот Муравей	458	Gadgets, Robots, Sports	46	01	January	First Tertile	458	Winter	46	Sergiev Posad Shopping Center "7Ya"	2013
Voronezh	5	2013-01-01 00:00:00	0	01	Tuesday	Music	Music - Music Video	59	14	1	18525	СБОРНИК Эх Разгуляй Всенародная танцплощадка 2010VD	169	Music Video	43	01	January	First Tertile	169	Winter	8	Voronezh City-Park Shopping Center "Grad"	2013
Kazan	8	2013-01-01 00:00:00	0	01	Tuesday	PC Games	PC Games - Additional Editions	28	9	1	2321	Call ofuty: Black Ops II. Расширенное издание [PС, русская версия]	999	Additional Editions	14	01	January	First Tertile	999	Winter	14	Kazan Shopping Center ParkHouse II	2013
Moscow	13	2013-01-01 00:00:00	0	01	Tuesday	Gifts	Gifts - Development	67	15	1	1025	3D Crystal Puzzle Свинья XL	499	Development	51	01	January	First Tertile	499	Winter	27	Moscow TC "MEGA Belaya Dacha II"	2013

Feature	Description
City	The City that a shop is located
City ID	Assigned numeric ID for a city
Date	Date in format dd/mm/yyyy
Date Block Number	A consecutive month number, used for convenience. January 2013 is 0, february 2013 is 1,..., October 2015 is 33
Day	Day of month
Day of Week	Day of Week
Item Category	Item Category
Item Category Full	Item Category and Subcategory Name
Item Category Full ID	Assigned numeric ID for a Item Category
Item Category ID	Unique identifier of item category
Item Count Day	The number of products sold. You are predicting a monthly amount of this measure
Item ID	Unique identifier of a product
Item Name	The name of item
Item Price	Current price of an item
Item Subcategory	Item Subcategory
Item Subcategory ID	Assigned numeric ID for a Item Subcategory
Month	Month
Month Name	Month Name
Month Triannually	Month Triannually
Revenue	Revenue
Season	Season
Shop ID	Unique identifier of a shop
Shop Name	The name of shop
Year	Year

def Sale_Plot(Group, PD):
    Cols = PD['Cols']
    fig = make_subplots(rows=2, cols=1, vertical_spacing = 0.05, shared_xaxes=True,
                        subplot_titles=['<b>%s<b>' % x for x in Cols])
    fig.update_xaxes(showline=True, linewidth=1, linecolor='Lightgray', mirror=True)
    fig.update_yaxes(showline=True, linewidth=1, linecolor='Lightgray', mirror=True,
                     showgrid=True, gridwidth=1, gridcolor='Lightgray')
    for c in range(len(Cols)):
        fig.add_trace(go.Bar(name = Cols[c], x= Group[PD['Feat']], y= Group[Cols[c]],
                             marker_color= [PD['Colors'][c]]* Group.shape[0],
                             text= Group[Cols[c]], textposition='inside',showlegend = False), row=c+1, col=1)
        fig.update_yaxes(range=[0, PD['Lims'][c]], row=c+1, col=1)

    fig.update_layout(legend_orientation='v', plot_bgcolor= 'white', height= PD['H'], width= 980,
                         title={'text': '<b>' + PD['Title'] + '<b>', 'x':.5, 'y': .95, 'xanchor': 'center', 'yanchor': 'top'})
    fig.update_traces(marker_line_color= 'Black', marker_line_width=1.2, opacity=1)
    fig.show()

Sale Plots by Date Block Interval

Group = Data.groupby(['Date Block Number'], as_index=False).agg({'Date':['min', 'max'],
                                     'Item Price':'sum', 'Item Count Day':'sum'})
Temp = Group.T.reset_index(drop = False).T
Temp = Temp.iloc[0,:] +' ' + Temp.iloc[1,:].apply(lambda x: x.title())
Group.columns = Temp
del Temp
Group['Date Interval'] = Group['Date Min'].dt.strftime('%B %d, %Y')+ ' - ' + Group['Date Max'].dt.strftime('%B %d, %Y')
Group.drop(columns = ['Date Min', 'Date Max'], inplace = True)
Group = Group.reindex(sorted(Group.columns), axis=1)
Group = Group.rename(columns = {'Item Count Day Sum': 'Total Products Sold', 'Item Price Sum':'Total Earned Amount'})

PD = dict(Feat = 'Date Interval', Title = 'Sale Plots by Date Block Interval', H = 850,
          Cols = Group.iloc[:,-2:].columns.tolist(), Colors = ['DarkViolet', 'RoyalBlue','ForestGreen'], Lims = [2e5, 2e8])
Sale_Plot(Group, PD)

Sale Plots by Year and Month

Temp = Data.copy()
Temp['Month'] = Data['Date'].dt.strftime('%Y %m: %B')
Group = Temp.groupby(['Month'], as_index=False).agg({'Item Price':'sum', 'Item Count Day':'sum'})
del Temp
Group = Group.reindex(sorted(Group.columns), axis=1)
Group = Group.rename(columns = {'Item Count Day': 'Total Products Sold', 'Item Price':'Total Earned Amount'})

PD = dict(Feat = 'Month', Title = 'Sale Plots by Year and Month', H = 850, Lims = [2e5, 2e8],
          Cols = Group.drop(columns = ['Month']).columns.tolist(), Colors = ['DarkViolet', 'RoyalBlue','ForestGreen'])
Sale_Plot(Group, PD)

Sale Plots by Season and Month

Group = Data.groupby(['Season', 'Month Name', 'Month Triannually'])\
        .agg({'Item Price':'sum', 'Item Count Day':'sum'}).dropna().reset_index(drop = False)\
        .sort_values(['Season', 'Month Name', 'Month Triannually'])
Group = Group.rename(columns = {'Item Count Day': 'Total Products Sold', 'Item Price':'Total Earned Amount'})
Group['Label'] = Group.iloc[:,0].astype(str) + ' - ' + Group.iloc[:,1].astype(str) + ' - ' + Group.iloc[:,2].astype(str)
Group = Group.iloc[:,3:]
Group = Group.loc[Group['Total Products Sold']>0]


PD = dict(Feat = 'Label', Title = 'Sale Plots by Season and Month', H = 850, Lims = [2e5, 2e8],
          Cols = ['Total Products Sold','Total Earned Amount'] , Colors = ['DarkViolet', 'RoyalBlue','ForestGreen'])
Sale_Plot(Group, PD)

Best Selling Shops

def Sale_Plot2(Group, PD):
    Cols = PD['Cols']
    fig = make_subplots(rows=2, cols=1, vertical_spacing = 0.05, shared_xaxes=True,
                        subplot_titles=['<b>%s<b>' % x for x in Cols])
    fig.update_xaxes(showline=True, linewidth=1, linecolor='Lightgray', mirror=True)
    fig.update_yaxes(showline=True, linewidth=1, linecolor='Lightgray', mirror=True,
                     showgrid=True, gridwidth=1, gridcolor='Lightgray')
    fig.update_layout(legend=dict(x=1.02, y=0.5))
    Cols = PD['Cols']
    for c in range(len(Cols)):
        fig0 = px.bar(Group, y= Cols[c], x= PD['Feat'], hover_data= Group.columns,
                     color = 'Year', color_discrete_sequence= PD['Colors'] , text = Cols[c])
        for j in range(len(fig0['data'])):
            fig.add_trace(fig0['data'][j], row=c+1, col=1)
            fig.update_yaxes(range=[0, PD['Lims'][c]], row=c+1, col=1)
        if c>0:
            fig.update_traces(showlegend = False, row=c+1, col=1)
        del fig0

    fig.update_traces(marker_line_color= 'Black', marker_line_width=1.2, opacity=1)
    fig.update_layout(legend_orientation='v', plot_bgcolor= 'white', height= PD['H'], width= 980,
                             title={'text': '<b>' + PD['Title'] + '<b>', 'x':.5, 'y': .95,
                                    'xanchor': 'center', 'yanchor': 'top'})
    fig.show()

Top = 15
Group = Data.groupby(['Year','Shop Name'], as_index=False).agg({'Item Price':'sum', 'Item Count Day':'sum'})
Group = Group.reindex(sorted(Group.columns), axis=1)
Group = Group.rename(columns = {'Item Count Day': 'Total Products Sold', 'Item Price':'Total Earned Amount'})
Best_Selling = Group.sort_values('Total Products Sold', ascending=False)['Shop Name'].unique().tolist()[:Top]
Group = Group.loc[Group['Shop Name'].isin(Best_Selling)].reset_index(drop = True)
Group['Year'] = Group['Year'].astype(str)

PD = dict(Feat = 'Shop Name', Title = 'Best Selling Shops', H = 850, Lims = [1.4e5, 8e7],
          Cols = Group.drop(columns =['Year','Shop Name']).columns.tolist() , Colors = ['LightGreen', 'LimeGreen', 'SeaGreen'])
Sale_Plot2(Group, PD)

Best Selling Cities

Top = 15
Group = Data.groupby(['Year','City'], as_index=False).agg({'Item Price':'sum', 'Item Count Day':'sum'})
Group = Group.reindex(sorted(Group.columns), axis=1)
Group = Group.rename(columns = {'Item Count Day': 'Total Products Sold', 'Item Price':'Total Earned Amount'})
Best_Selling = Group.sort_values('Total Products Sold', ascending=False)['City'].unique().tolist()[:Top]
Group = Group.loc[Group['City'].isin(Best_Selling)].reset_index(drop = True)
Group['Year'] = Group['Year'].astype(str)

PD = dict(Feat = 'City', Title = 'Best Selling Shops', H = 800, Lims = [6e5, 4e8],
          Cols = Group.drop(columns =['Year','City']).columns.tolist() , Colors = ['LavenderBlush','LightPink','HotPink'])
Sale_Plot2(Group, PD)

Best Selling Categories

Group = Data.groupby(['Item Category'], as_index=False).agg({'Item Price':'sum', 'Item Count Day':'sum'})
Group = Group.reindex(sorted(Group.columns), axis=1)
Group = Group.rename(columns = {'Item Count Day': 'Total Products Sold', 'Item Price':'Total Earned Amount'})
Group = Group.sort_values('Total Products Sold', ascending=False)

PD = dict(Feat = 'Item Category', Title = 'Best Selling Shops', H = 800, Lims = [1e6, 1e9],
          Cols = ['Total Products Sold', 'Total Earned Amount'] , Colors =['DarkViolet', 'RoyalBlue','ForestGreen'])
Sale_Plot(Group, PD)

---

References

1. Kaggle Dataset: Predict Future Sales
2. Plotly Documentation

---